#!/usr/bin/python3

import os
import pymysql
import pyecharts.options as opts
from pyecharts.charts import Line, Grid

##connect info
mtsql_host = os.getenv('MTSQL_METDATA_HOST')
mtsql_port_str = os.getenv('MTSQL_METDATA_PORT')
mtsql_port = int(mtsql_port_str)
mtsql_user = os.getenv('MTSQL_METDATA_USER')
mtsql_password = os.getenv('MTSQL_METDATA_PASSWORD')
mtsql_database = os.getenv('MTSQL_METDATA_DATABASE')
mtsql_charset = os.getenv('MTSQL_METDATA_CHARSET')

##test info
mtsql_pt_result_graph_path=os.getenv('MTSQL_PT_RESULT_GRAPH_PATH')
mtsql_pt_feature = os.getenv('MTSQL_PT_FEATURE')
mtsql_pt_with_feature = os.getenv('MTSQL_PT_WITH_FEATURE')
mtsql_pt_test_scenarios = os.getenv('MTSQL_PT_TEST_SCENARIOS')
mtsql_pt_sub_test_scenarios = os.getenv('MTSQL_PT_SUB_TEST_SCENARIOS')
mtsql_pt_db_type = os.getenv('MTSQL_PT_DB_TYPE')
mtsql_pt_test_case = os.getenv('MTSQL_PT_TEST_CASE')
mtsql_pt_test_batch_no = os.getenv('MTSQL_PT_TEST_BATCH_NO')
mtsql_pt_os_name = os.getenv('MTSQL_PT_OS_NAME')
mtsql_pt_machine_hardware_name = os.getenv('MTSQL_PT_MACHINE_HARDWARE_NAME')
mtsql_pt_deploy_type = os.getenv('MTSQL_PT_DEPLOY_TYPE')
mtsql_pt_deploy_type = mtsql_pt_deploy_type.lower()

#loop
mtsql_pt_test_cases= ['oltp_read_write', 'oltp_read_only', 'oltp_update_non_index', 'oltp_point_select', 'oltp_write_only']
#mtsql_pt_test_cases= ['oltp_read_write']

#key name
mtsql_tps_name='tps'
mtsql_qps_name='qps'

db= pymysql.connect(host=mtsql_host, port=mtsql_port, user=mtsql_user, password=mtsql_password, database=mtsql_database, charset=mtsql_charset)

cursor = db.cursor()

sql= "SELECT threads,value " \
     "FROM pt_feature_result_info " \
     "where feature = %s " \
     "and with_feature= %s " \
     "and test_scenarios= %s " \
     "and sub_test_scenarios= %s " \
     "and db_type= %s " \
     "and test_case= %s " \
     "and test_batch_no= %s " \
     "and os_name= %s " \
     "and machine_hardware_name= %s " \
     "and deploy_type= %s " \
     "and name= %s " \
     "order by CAST(threads as UNSIGNED)"

for mtsql_pt_test_case in mtsql_pt_test_cases:

    try:
        print("result trasaction base graph for test case=", mtsql_pt_test_case)
        #common
        graph_file_name = mtsql_pt_result_graph_path \
                        + "/pt_result_trasaction_base_" \
                        + mtsql_pt_deploy_type \
                        + "_" \
                        + mtsql_pt_feature \
                        + "_" \
                        + mtsql_pt_with_feature \
                        + "_" \
                        + mtsql_pt_test_scenarios \
                        + mtsql_pt_test_batch_no \
                        + "_" \
                        + mtsql_pt_db_type \
                        + "_" \
                        + mtsql_pt_test_case \
                        + ".html"
        ##query tps info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_tps_name)
        mtsql_version_list = []
        mtsql_tps_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_tps_list.append(value_float)
        #graph
        print("result graph for tps!")
        tps_line_max = max(mtsql_tps_list)
        tps_line_max = int(tps_line_max / 100) * 100
        tps_line_max = tps_line_max + 500
        tps_line_min = min(mtsql_tps_list)
        tps_line_min = int(tps_line_min / 100) * 100
        tps_line_min = tps_line_min - 500
        tps_line = (
            Line()
                .add_xaxis(xaxis_data=mtsql_version_list)
                .add_yaxis(
                    series_name="结果值",
                    y_axis=mtsql_tps_list,
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                            opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                        ]
                    ),
                    markline_opts=opts.MarkLineOpts(
                        data=[opts.MarkLineItem(type_="average", name="平均值")]
                    ),
                    is_smooth=True,
                )
                .set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                                          name_gap=5,
                                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                                 yaxis_opts=opts.AxisOpts(
                                     name="tps",
                                     max_=tps_line_max,
                                     min_=tps_line_min,
                                     max_interval=1000,
                                     min_interval=100),
                                 )
        )

        ##query qps info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_qps_name)
        mtsql_version_list = []
        mtsql_qps_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_qps_list.append(value_float)
        # graph
        print("result graph for qps!")
        qps_line_max = max(mtsql_qps_list)
        qps_line_max = int(qps_line_max / 1000) * 1000
        qps_line_max = qps_line_max + 5000
        qps_line_min = min(mtsql_qps_list)
        qps_line_min = int(qps_line_min / 1000) * 1000
        qps_line_min = qps_line_min - 5000
        qps_line = (
            Line()
                .add_xaxis(xaxis_data=mtsql_version_list)
                .add_yaxis(
                    series_name="结果值",
                    y_axis=mtsql_qps_list,
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                            opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                        ]
                    ),
                    markline_opts=opts.MarkLineOpts(
                        data=[opts.MarkLineItem(type_="average", name="平均值")]
                    ),
                    is_smooth=True,
                )
                .set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                                          name_gap=5,
                                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                                 yaxis_opts=opts.AxisOpts(
                                     name="qps",
                                     max_=qps_line_max,
                                     min_=qps_line_min,
                                     max_interval=50000,
                                     min_interval=10000),
                                 )
        )

        grid = (
            Grid(init_opts=opts.InitOpts(width="1500px", height="800px"))
                .add(tps_line,
                     grid_opts=opts.GridOpts(pos_top="10%", pos_bottom="5%", pos_left="5%", pos_right="55%"))
                .add(qps_line,
                     grid_opts=opts.GridOpts(pos_top="10%", pos_bottom="5%", pos_left="55%", pos_right="5%"))
                .render(graph_file_name)
        )
    except Exception as e:
        print("result transaction base graph catch exception!")
        print(e)

db.close()

